package org.sjlt.marc.dataservices;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.hibernate.annotations.common.util.StringHelper;
import org.json.simple.JSONObject;
import org.sjlt.marc.common.BigHelpDBConnection;
import org.sjlt.marc.common.Queries;
import org.sjlt.marc.resources.Meeting;

public class ReportDataService extends HttpServlet 
{
	private static final long serialVersionUID = 1L;
       
    public ReportDataService() 
    {
        super();
    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException 
    {
		doPost(request,response);
	}
    
	@SuppressWarnings("unchecked")
	@Override
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException 
	{
		if(StringHelper.isNotEmpty(request.getParameter("ReportType")))
		{
			response.setContentType("application/json");
			PrintWriter pw = response.getWriter();
			String requestType = request.getParameter("ReportType");
			BigHelpDBConnection bhdbcon = new BigHelpDBConnection("sjlt_marc");
			
			if("attendanceBasic".equals(requestType))
			{
				Vector<Meeting> mtgData = new Vector<Meeting>();
				try
				{
					JSONObject output = new JSONObject();
					Connection conn = bhdbcon.getConnection();
					PreparedStatement ps1 = conn.prepareStatement(Queries.SQL_TOP_6_MTGS);
					PreparedStatement ps2 = conn.prepareStatement(Queries.SQL_TOP_6_MTGS_ATTENDANCE);
					PreparedStatement ps3 = conn.prepareStatement(Queries.SQL_DROP_MTGS_TABLE);
					
					try
					{
						Integer result = ps1.executeUpdate();
						if(result > 0)
						{
							ResultSet rs = ps2.executeQuery();
							try
							{
								while(rs.next())
								{
									Integer mtgID = rs.getInt("MeetingID");
									Date startDate = rs.getDate("StartDate");
									Integer attendees = rs.getInt("Attendees");
									
									mtgData.add(new Meeting(mtgID, "1", startDate, startDate, "", "", attendees));
								}

								output.put("Meetings", mtgData);
								pw.println(output.toString());
							}
							finally
							{
								rs.close();
								ps3.executeUpdate();
							}
						}
					}
					finally
					{
						ps1.close();
						ps2.close();
						ps3.close();
					}
				}
				catch(SQLException se)
				{
					se.printStackTrace();
				}
				finally
				{
					bhdbcon.closeConnection();
				}
			}
			else if("attendanceDetailed".equals(requestType))
			{
				Vector<Meeting> mtgData = new Vector<Meeting>();
				try
				{
					JSONObject output = new JSONObject();
					Connection conn = bhdbcon.getConnection();
					PreparedStatement ps1 = conn.prepareStatement(Queries.SQL_TOP_6_MTGS);
					PreparedStatement ps2 = conn.prepareStatement(Queries.SQL_TOP_6_MTGS_ATTENDANCE);
					PreparedStatement ps3 = conn.prepareStatement(Queries.SQL_DROP_MTGS_TABLE);
					
					try
					{
						Integer result = ps1.executeUpdate();
						if(result > 0)
						{
							ResultSet rs = ps2.executeQuery();
							try
							{
								while(rs.next())
								{
									Integer mtgID = rs.getInt("MeetingID");
									Date startDate = rs.getDate("StartDate");
									Integer attendees = rs.getInt("Attendees");
									
									mtgData.add(new Meeting(mtgID, "1", startDate, startDate, "", "", attendees));
								}

								output.put("Meetings", mtgData);
								pw.println(output.toString());
							}
							finally
							{
								rs.close();
								ps3.executeUpdate();
							}
						}
					}
					finally
					{
						ps1.close();
						ps2.close();
						ps3.close();
					}
				}
				catch(SQLException se)
				{
					se.printStackTrace();
				}
				finally
				{
					bhdbcon.closeConnection();
				}
			}
			else if("aliasUsage".equals(requestType))
			{
				Vector<Meeting> mtgData = new Vector<Meeting>();
				try
				{
					JSONObject output = new JSONObject();
					Connection conn = bhdbcon.getConnection();
					PreparedStatement ps = conn.prepareStatement(Queries.SQL_TOP_6_MTGS);
					
					try
					{
							ResultSet rs = ps.executeQuery();
							try
							{
								while(rs.next())
								{
									Integer mtgID = rs.getInt("MeetingID");
									Date startDate = rs.getDate("StartDate");
									Integer attendees = rs.getInt("Attendees");
									
									mtgData.add(new Meeting(mtgID, "1", startDate, startDate, "", "", attendees));
								}

								output.put("Meetings", mtgData);
								pw.println(output.toString());
							}
							finally
							{
								rs.close();
							}
					}
					finally
					{
						ps.close();
					}
				}
				catch(SQLException se)
				{
					se.printStackTrace();
				}
				finally
				{
					bhdbcon.closeConnection();
				}
			}
			/*else if("requestTypeString".equals(requestType)
			{
				other functions;
			}*/
		}
	}
}
